package com.liu.repository;

import com.liu.domain.JiFen;
import com.liu.domain.room;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.List;

//

@Repository
public interface JiFenRepository extends JpaRepository<JiFen, Integer>{

    @Transactional
    @Modifying
    @Query(value = "insert into ji_fen(years, terms, room_id) select ?1, ?2, id from room", nativeQuery = true)

    // 初始化一个学期内的寝室分数
    int initJifenData(int years, int terms);

    List<JiFen>findAllByYearsAndTerms(int year, int term);
    @Query(value = "select * from ji_fen where room_id=?1 and years=?2 and terms=?3", nativeQuery = true)
    JiFen findByRoomIdAndYearsAndTerms(int id, int years, int term);

    //卫生加减分
    @Modifying
    @Transactional
    @Query(value = "update ji_fen set score=score+?2 where room_id=?1 and years=?3 and terms=?4", nativeQuery = true)
    int modifyRoomScore(int roomid, int score, int year, int term);

    //违规 扣分
    @Modifying
    @Transactional
    @Query(value = "update ji_fen set score=score-2 where room_id=?1 ", nativeQuery = true)
    int modifyRoomScoreRule(int R_id);

    //到时间删除所有数据
    @Modifying
    @Transactional
    @Query(value = "DELETE from ji_fen  where years=?1 AND terms = ?2", nativeQuery = true)
    int dateDel(int year,int term);

    //按寝室查询积分
    @Query(value ="select * from ji_fen  where room_id = ?1" , nativeQuery = true)
    public List<JiFen> findByRoom(int room_id);

    //查询积分排名
    @Query(value ="select * from ji_fen ORDER BY score DESC" , nativeQuery = true)
    public List<JiFen> findPM();
}
